﻿using System;
using System.Collections.Generic;
using System.Linq;
using CSC440TeamOne.CSC440BusinessService.Interface.Services;

namespace CSC440TeamOne.CSC440BusinessService.Services
{
    public class SubsonicDataService : IDataService
    {
        private SubSonic.CSC440DBDB db;

        public SubsonicDataService()
        {
            db = new SubSonic.CSC440DBDB();
        }

        public IEnumerable<Common.TrackableHealthParameter> LoadTrackableHealthParameterProgressForUser(Common.User user)
        {
            return from t in db.TrackableHealthParameters
                   join h in db.HealthParameters on t.HealthParameterId equals h.Id
                   where t.UserId == user.UserId
                   select new Common.TrackableHealthParameter()
                   {
                       EnteredOn = t.EnteredOn,
                       ID = t.Id,
                       MaxValue = t.MaxValue,
                       MinValue = t.MinValue,
                       status = t.Status,
                       Value = t.Value,
                       Name = h.Name
                   };
        }

        public IEnumerable<Common.HealthGoal> FindHeathGoalsForUser(Common.User user)
        {
            return from h in db.HealthGoals
                   join htu in db.HealthGoalToUsers on h.Id equals htu.HealthGoalId
                   select new Common.HealthGoal()
                   {
                       Description = h.Description,
                       ID = h.Id,
                       Selected = h.Selected
                   };
        }

        public IEnumerable<Common.TrackableHealthParameter> LoadDailyTrackableHealthParametersForUser(Common.User user)
        {
            return from t in db.TrackableHealthParameters
                   join h in db.HealthParameters on t.HealthParameterId equals h.Id
                   where t.UserId == user.UserId
                   select new Common.TrackableHealthParameter()
                   {
                       EnteredOn = t.EnteredOn,
                       ID = t.Id,
                       MaxValue = t.MaxValue,
                       MinValue = t.MinValue,
                       status = t.Status,
                       Value = t.Value,
                       Name = h.Name
                   };
        }

        public void SaveUserHealthGoal(Common.User user, Common.HealthGoal healthGoal)
        {
            if (healthGoal.Selected)
            {
                db.Insert.Into<SubSonic.HealthGoalToUser>(
                    x => x.HealthGoalId,
                    x => x.UserId)
                    .Values(healthGoal.ID, user.UserId)
                    .Execute();
            }
            else
            {
                db.Delete<SubSonic.HealthGoalToUser>(
                    x => x.HealthGoalId == healthGoal.ID && x.UserId == user.UserId)
                    .Execute();
            }
        }

        public IEnumerable<Common.HealthParameter> LoadAllHealthParameters()
        {
            return from h in db.HealthParameters
                   select new Common.HealthParameter()
                   {
                       Description = h.Description,
                       Formula = h.Formula,
                       ID = h.Id,
                       Name = h.Name
                   };
        }

        public Common.HealthParameter FindHealthParameterById(int healthParameterID)
        {
            return (from h in db.HealthParameters
                    where h.Id == healthParameterID
                    select new Common.HealthParameter()
                    {
                        Description = h.Description,
                        Formula = h.Formula,
                        ID = h.Id,
                        Name = h.Name
                    }).FirstOrDefault();
        }

        public void Update(Common.HealthParameter healthParameter)
        {
            db.Update<SubSonic.HealthParameter>()
                .Set(x => x.Description == healthParameter.Description,
                x => x.Formula == healthParameter.Formula,
                x => x.Name == healthParameter.Name)
                .Where(x => x.Id == healthParameter.ID)
                .Execute();
        }

        public int InsertDailyHealthParameterForUser(Common.TrackableHealthParameter dailyHealthParameter, Common.User user)
        {
            db.Insert.Into<SubSonic.TrackableHealthParameter>(
                x => x.EnteredOn,
                x => x.HealthParameterId,
                x => x.MaxValue,
                x => x.MinValue,
                x => x.Status,
                x => x.UserId,
                x => x.Value)
                .Values(DateTime.Now,
                dailyHealthParameter.HealthParameterId,
                dailyHealthParameter.MaxValue,
                dailyHealthParameter.MinValue,
                dailyHealthParameter.status,
                user.UserId,
                dailyHealthParameter.Value)
                .Execute();

            return db.TrackableHealthParameters.Max(t => t.Id);
        }

        public IEnumerable<Common.UserManualSection> LoadAllUserManualSections()
        {
            return from s in db.UserManualSections
                   select new Common.UserManualSection()
                   {
                       Id = s.Id,
                       Parent = s.Parent,
                       Title = s.Title
                   };
        }

        public IEnumerable<Common.UserManualContent> FindForUserManualSection(Common.UserManualSection userManualSection)
        {
            return from c in db.UserManualContents
                   where c.UserManualSectionId == userManualSection.Id
                   select new Common.UserManualContent()
                   {
                       Caption = c.Caption,
                       Content = c.Content,
                       Deleted = c.Deleted,
                       Id = c.Id,
                       Image = c.Image,
                       OrderIndex = c.OrderIndex,
                       Title = c.Title,
                       UserManualSectionId = c.UserManualSectionId
                   };
        }

        public int InsertUserManualSection(Common.UserManualSection currentUserManualSection)
        {
            db.Insert.Into<SubSonic.UserManualSection>(
                x => x.Parent,
                x => x.Title)
                .Values(currentUserManualSection.Parent, currentUserManualSection.Title)
                .Execute();

            return db.UserManualSections.Max(i => i.Id);
        }

        public void UpdateUserManualSection(Common.UserManualSection currentUserManualSection)
        {
            db.Update<SubSonic.UserManualSection>()
                .Set(x => x.Parent == currentUserManualSection.Parent,
                x => x.Title == currentUserManualSection.Title)
                .Where(x => x.Id == currentUserManualSection.Id)
                .Execute();
        }

        public int InsertUserManualContent(Common.UserManualContent userManualContent)
        {
            db.Insert.Into<SubSonic.UserManualContent>(
                x => x.Caption,
                x => x.Content,
                x => x.Deleted,
                x => x.Image,
                x => x.OrderIndex,
                x => x.Title,
                x => x.UserManualSectionId)
                .Values(userManualContent.Caption, 
                userManualContent.Content, 
                userManualContent.Deleted, 
                userManualContent.Image, 
                userManualContent.OrderIndex, 
                userManualContent.Title, 
                userManualContent.UserManualSectionId)
                .Execute();

            return db.UserManualContents.Max(x => x.Id);
        }

        public void UpdateUserManualContent(Common.UserManualContent userManualContent)
        {
            db.Update<SubSonic.UserManualContent>()
                .Set(x => x.Caption == userManualContent.Caption,
                x => x.Content == userManualContent.Content,
                x => x.Deleted == userManualContent.Deleted,
                x => x.Image == userManualContent.Image,
                x => x.OrderIndex == userManualContent.OrderIndex,
                x => x.Title == userManualContent.Title,
                x => x.UserManualSectionId == userManualContent.UserManualSectionId)
                .Where(x => x.Id == userManualContent.Id)
                .Execute();
        }

        public Common.Recipe FindRecipeByID(int recipeID)
        {
            var result = (from r in db.Recipes
                          where r.RecipeID == recipeID
                          select new Common.Recipe()
                          {
                              dateSubmitted = DateTime.Parse(r.RecipeSubmitDate),
                              directions = r.RecipeDirections,
                              RecipeID = r.RecipeID,
                              name = r.RecipeName,
                              servings = r.RecipeServing.ToString(),
                              ingredients = (from i in db.RecipeIngredients
                                             where i.RecipeID == r.RecipeID
                                             select new Common.RecipeIngredient()
                                             {
                                                 ingredient = new Common.Ingredient()
                                                 {
                                                     name = i.Ingredient
                                                 },
                                                 measurement = i.IngredientMeasurement,
                                                 quantity = i.IngredientQuantity
                                             }).ToList(),
                              createdBy = GetUserByUserId(r.UserID)
                          }).FirstOrDefault();

            if (result != null)
            {
                result.ingredients = (from i in db.RecipeIngredients
                                      where i.RecipeID == recipeID
                                      select new Common.RecipeIngredient()
                                      {
                                          ingredient = new Common.Ingredient() { name = i.Ingredient },
                                          measurement = i.IngredientMeasurement,
                                          quantity = i.IngredientQuantity
                                      }).ToList();
            }

            return result;
        }

        public List<Common.TrackableHealthParameter> LoadCurrentTrackableHealthParametersForUser(Common.User u)
        {
            throw new NotImplementedException();
        }

        public List<Common.Question> populateQuestionList()
        {
            return (from q in db.Questions
                    join h in db.HelpTexts on q.HelpTextID equals h.HelpTextID
                    select new Common.Question()
                    {
                        AnswerControlType = (Common.Question.Types)Enum.Parse(typeof(Common.Question.Types), q.AnswerControlTypeID.ToString()),
                        Answered = q.Answered.Value,
                        AnswerText = q.AnswerText,
                        //ChildItems
                        HelpText = new Common.HelpText()
                        {
                            Description = h.Description,
                            Header = h.Header
                        },
                        Locked = q.Locked.Value,
                        PageID = q.PageID.Value,
                        ParentQuestionID = q.ParentQuestionID.Value,
                        QuestionID = q.QuestionID,
                        QuestionText = q.QuestionText,
                        RegExMessage = q.RegExMessage,
                        RegExValidation = q.RegExValidation,
                        Required = q.Required.Value,
                        RequiredMessage = q.RequiredMessage,
                        ShowOnPrintout = q.ShowOnPrintout.Value
                    }).ToList();
        }

        public List<Common.Recipe> LoadListOfSubmittedRecipes()
        {
            return (from r in db.Recipes
                    where r.Approvided
                    select new Common.Recipe()
                    {
                        createdBy = GetUserByUserId(r.UserID),
                        dateSubmitted = DateTime.Parse(r.RecipeSubmitDate),
                        directions = r.RecipeDirections,
                        name = r.RecipeName,
                        RecipeID = r.RecipeID,
                        servings = r.RecipeServing.ToString(),
                        ingredients = (from i in db.RecipeIngredients
                                       where i.RecipeID == r.RecipeID
                                       select new Common.RecipeIngredient()
                                       {
                                           ingredient = new Common.Ingredient()
                                           {
                                               name = i.Ingredient
                                           },
                                           measurement = i.IngredientMeasurement,
                                           quantity = i.IngredientQuantity
                                       }).ToList()
                    }).ToList();
        }


        private Common.User GetUserByUserId(Guid userId)
        {
            return (from u in db.aspnet_Users
                    join cu in db.CSC_Users on u.UserId equals cu.UserId
                    join ur in db.aspnet_UsersInRoles on u.UserId equals ur.UserId
                    join r in db.aspnet_Roles on ur.RoleId equals r.RoleId
                    where u.UserId == userId
                    select new Common.User()
                    {
                        BirthDate = DateTime.Parse(cu.BirthDay),
                        Country = cu.Country,
                        EmailAddress = cu.EmailAddress,
                        Ethnicity = cu.Ethnicity,
                        FirstName = cu.FirstName,
                        Gender = cu.Gender,
                        LastName = cu.LastName,
                        QuestionaireCompleted = cu.QuestionaireCompleted,
                        QuestionaireLastPageCompleted = cu.QuestionaireLastPageCompleted,
                        RegistrationConfirmed = cu.RegistrationConfirmed,
                        UserRole = (Common.UserRole)Enum.Parse(typeof(Common.UserRole), r.RoleName),
                        Zipcode = cu.Zipcode

                    }).FirstOrDefault();
        }


        public Common.MyFoodTrackerViewModel LoadMyFoodTrackerRecipes()
        {
            throw new NotImplementedException();
        }

        public List<Common.Recipe> LoadPrintRecipe(int recipeID)
        {
            throw new NotImplementedException();
        }

        public Common.Recipe LoadPrintRecipe2(int id)
        {
            throw new NotImplementedException();
        }
    }
}
